package rubix.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ItemManager {

    private static final String TBLNAME = "item";

    private static void handleSQLException(SQLException ex, String sql, String... parameters) {
        String msg = "Unable to access data; SQL=" + sql + "\n";

        for (String parameter : parameters) {
            msg += "," + parameter;
        }

        Logger.getLogger(ItemManager.class.getName()).log(Level.SEVERE, msg, ex);
        throw new RuntimeException(msg, ex);
    }
    //method
    public static void prepareTables(){
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        try {
            conn = ConnectionManager.getConnection();            
            
            sql = "DROP TABLE IF EXISTS item";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            
            sql = "CREATE TABLE IF NOT EXISTS item("
                    + "itemid VARCHAR(25) NOT NULL,"
                    + "supplierid VARCHAR(20) NOT NULL,"
                    + "itemdescription VARCHAR(50) NOT NULL,"
                    + "partid VARCHAR(50) NOT NULL,"                     					
                    + "PRIMARY KEY (itemid)"
                    + ")";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "prepareTables");
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }    
    
    public static void addItem(Item item) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        String itemId = item.getItemId();
        String supplierId = item.getSupplierId();
        String itemDescription = item.getItemDescription();
        String partId = item.getPartId();
        
        try {
            conn = ConnectionManager.getConnection();
            
            sql = "INSERT INTO " + TBLNAME + " (itemid,supplierid,itemdescription,partid) VALUES (?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, itemId);
            stmt.setString(2, supplierId);
            stmt.setString(3, itemDescription);
            stmt.setString(4, partId);
            
            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "itemid=" + itemId + ",supplierid=" + supplierId + ",itemdescription=" + itemDescription 
                + ",partId=" + partId);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }
    
    public static ArrayList<Item> retrieveAll() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<Item> results = new ArrayList<Item>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT itemid,supplierid,itemdescription,partid FROM " + TBLNAME;
            stmt = conn.prepareStatement(sql);

            rs = stmt.executeQuery();
            while (rs.next()) {
                String itemId = rs.getString("itemid");
                String supplierId = rs.getString("supplierid");
                String itemDescription = rs.getString("itemdescription");
                String partId = rs.getString("partid");

                results.add(new Item(itemId,supplierId,itemDescription,partId));
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return results;
    }

    public static Item retrieveItem(String itemId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        Item result = null;

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT itemid,supplierid,itemdescription,partid FROM " + TBLNAME + " WHERE itemid = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, itemId);

            rs = stmt.executeQuery();

            while (rs.next()) {
                String supplierId = rs.getString("supplierid");
                String itemDescription = rs.getString("itemdescription");
                String partId = rs.getString("partid");

                result = new Item(itemId,supplierId,itemDescription,partId);
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "itemid=" + itemId);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return result;
    }
    
    public static ArrayList retrieveItemList(String supplierId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<Item> result = new ArrayList<Item>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT itemid,supplierid,itemdescription,partid FROM " + TBLNAME + " WHERE supplierid = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, supplierId);

            rs = stmt.executeQuery();

            while (rs.next()) {
                String itemId = rs.getString("itemid");
                String itemDescription = rs.getString("itemdescription");
                String partId = rs.getString("partid");

                result.add(new Item(itemId,supplierId,itemDescription,partId));
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "supplierid=" + supplierId);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return result;
    }
    
    /*
    public static void updateOnOrderQuantity(String poId, int onOrderQuantity) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";

        try {
            conn = ConnectionManager.getConnection();

            sql = "UPDATE " + TBLNAME + " set onorderquantity=? where poid = ?";
            stmt = conn.prepareStatement(sql);

            stmt.setInt(1, onOrderQuantity);
            stmt.setString(2, poId);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "poid=" + poId + ",onorderquantity=" + onOrderQuantity);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }*/
}